package lx;

import java.io.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import Connection.*;


public class ActivitiesDAOImpl extends DAOBase implements ActivitiesDAO{
    private static String ADD_SQL="INSERT INTO activities(activities_approval_1,activities_approval_2,activities_name,activities_place,activities_time,activities_report,activities_mes,Student_id,material) VALUES(?,?,?,?,?,?,?,?,?)";

    private static String GETALL_SQL="SELECT * FROM activities WHERE student_id=?";

    private static String GETONE_SQL="SELECT * FROM activities WHERE activities_id=?";

    private static String DELETE_SQL="DELETE FROM activities WHERE activities_id=?";

    private static String APPROVAL_SQL_1="UPDATE activities SET activities_approval_1=? WHERE activities_id=?";

    private static String APPROVAL_SQL_2="UPDATE activities SET activities_approval_2=? WHERE activities_id=?";

    private static String UPDATE_NUM="UPDATE student SET communicate_num=communicate_num+1 WHERE student_id=?";
    private static String GETALL="SELECT * FROM activities";
    @Override
    public void addActivities(Activities activities) {
        Connection con = null;
        try {
            con = getConnection();
            PreparedStatement psmt = con.prepareStatement(ADD_SQL);
            psmt.setInt(1, activities.getActivities_approval_1());
            psmt.setInt(2, activities.getActivities_approval_2());
            psmt.setString(3, activities.getActivities_name());
            psmt.setString(4, activities.getActivities_place());
            psmt.setString(5, activities.getActivities_time());
            psmt.setString(6,activities.getActivities_report());
            psmt.setString(7,activities.getActivities_mes());
            psmt.setString(8,activities.getStudent_id());
            File file = new File(activities.getMaterial_url());//将要存入数据库的文件
            InputStream is = new FileInputStream(file);
            psmt.setBinaryStream(9,is,file.length());
            psmt.executeUpdate();
            psmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public void approvalActivities_1(Boolean flag, int Activities_id) {
        Connection con = null;
        try {
            con = getConnection();
            PreparedStatement psmt = con.prepareStatement(APPROVAL_SQL_1);
            if(flag)
                psmt.setInt(1,1);
            else
                psmt.setInt(1,0);
            psmt.setInt(2,Activities_id);
            psmt.executeUpdate();
            psmt.close();
            Activities temp=getActivity(Activities_id);
            if(temp.getActivities_approval_1()==1&&temp.getActivities_approval_2()==1){
                psmt = con.prepareStatement(UPDATE_NUM);
                psmt.setString(1,temp.getStudent_id());
                psmt.executeUpdate();
                psmt.close();
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public void approvalActivities_2(Boolean flag, int Activities_id) {
        Connection con = null;
        try {
            con = getConnection();
            PreparedStatement psmt = con.prepareStatement(APPROVAL_SQL_2);
            if(flag)
                psmt.setInt(1,1);
            else
                psmt.setInt(1,0);
            psmt.setInt(2,Activities_id);
            psmt.executeUpdate();
            psmt.close();
            Activities temp=getActivity(Activities_id);
            if(temp.getActivities_approval_1()==1&&temp.getActivities_approval_2()==1){
                psmt = con.prepareStatement(UPDATE_NUM);
                psmt.setString(1,temp.getStudent_id());
                psmt.executeUpdate();
                psmt.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public void deleteActivities(int Activities_id) {
        Connection con = null;
        try {
            con = getConnection();
            PreparedStatement psmt = con.prepareStatement(DELETE_SQL);
            psmt.setInt(1, Activities_id);
            psmt.executeUpdate();
            psmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public Activities getActivity(int Activities_id) {
        Activities activities = null;
        Connection con = null;
        try {
            con = getConnection();
            PreparedStatement psmt = con.prepareStatement(GETONE_SQL);
            psmt.setInt(1, Activities_id);
            ResultSet rs = psmt.executeQuery();
            if (rs.next()) {
                activities = new Activities();
                activities.setActivities_id(Activities_id);
                activities.setActivities_approval_1(rs.getInt("activities_approval_1"));
                activities.setActivities_approval_2(rs.getInt("activities_approval_2"));
                activities.setActivities_name(rs.getString("activities_name"));
                activities.setActivities_place(rs.getString("activities_place"));
                activities.setActivities_time(rs.getString("activities_time"));
                activities.setActivities_report(rs.getString("activities_report"));
                activities.setActivities_mes(rs.getString("activities_mes"));
                activities.setStudent_id(rs.getString("student_id"));
                InputStream in = rs.getBinaryStream("material");
                if(in!=null){
                    activities.setMaterial_url("d:\\materials\\"+ activities.getStudent_id()+"_" + activities.getActivities_name() +".jpg");
                    DataOutputStream sos = new DataOutputStream(new BufferedOutputStream(new FileOutputStream(activities.getMaterial_url())));
                    int len = 0;
                    byte[] b = new byte[1024];
                    while ((len = in.read(b)) != -1) {
                        sos.write(b,0,len);
                    }
                    sos.close();
                    in.close();
                }

            }
            rs.close();
            psmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return activities;
    }


    @Override
    public List<Activities> getActivities(String Student_id) {
        List<Activities> activities = new ArrayList<>();
        Connection con = null;
        try {
            con = getConnection();
            PreparedStatement psmt = con.prepareStatement(GETALL_SQL);
            psmt.setString(1, Student_id);
            ResultSet rs = psmt.executeQuery();
            while (rs.next()) {
                Activities activity = new Activities();
                activity.setActivities_id(rs.getInt("activities_id"));
                activity.setActivities_approval_1(rs.getInt("activities_approval_1"));
                activity.setActivities_approval_2(rs.getInt("activities_approval_2"));
                activity.setActivities_name(rs.getString("activities_name"));
                activity.setActivities_place(rs.getString("activities_place"));
                activity.setActivities_time(rs.getString("activities_time"));
                activity.setActivities_report(rs.getString("activities_report"));
                activity.setActivities_mes(rs.getString("activities_mes"));
                activity.setStudent_id(rs.getString("student_id"));
                InputStream in = rs.getBinaryStream("material");
                if(in!=null){
                    activity.setMaterial_url("d:\\materials\\"+ activity.getStudent_id()+"_" + activity.getActivities_name() +".jpg");
                    DataOutputStream sos = new DataOutputStream(new BufferedOutputStream(new FileOutputStream(activity.getMaterial_url())));
                    int len = 0;
                    byte[] b = new byte[1024];
                    while ((len = in.read(b)) != -1) {
                        sos.write(b,0,len);
                    }
                    sos.close();
                    in.close();
                }

                activities.add(activity);
            }
            rs.close();
            psmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return activities;
    }

    public List<Activities> getAll() {
        List<Activities> activities = new ArrayList<>();
        Connection con = null;
        try {
            con = getConnection();
            PreparedStatement psmt = con.prepareStatement(GETALL_SQL);
            ResultSet rs = psmt.executeQuery();
            while (rs.next()) {
                Activities activity = new Activities();
                activity.setActivities_id(rs.getInt("activities_id"));
                activity.setActivities_approval_1(rs.getInt("activities_approval_1"));
                activity.setActivities_approval_2(rs.getInt("activities_approval_2"));
                activity.setActivities_name(rs.getString("activities_name"));
                activity.setActivities_place(rs.getString("activities_place"));
                activity.setActivities_time(rs.getString("activities_time"));
                activity.setActivities_report(rs.getString("activities_report"));
                activity.setActivities_mes(rs.getString("activities_mes"));
                activity.setStudent_id(rs.getString("student_id"));
                InputStream in = rs.getBinaryStream("material");
                if(in!=null){
                    activity.setMaterial_url("d:\\materials\\"+ activity.getStudent_id()+"_" + activity.getActivities_name() +".jpg");
                    DataOutputStream sos = new DataOutputStream(new BufferedOutputStream(new FileOutputStream(activity.getMaterial_url())));
                    int len = 0;
                    byte[] b = new byte[1024];
                    while ((len = in.read(b)) != -1) {
                        sos.write(b,0,len);
                    }
                    sos.close();
                    in.close();
                }

                activities.add(activity);
            }
            rs.close();
            psmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return activities;
    }
}
